package com.hdaccp.ch09

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._

import scala.collection.mutable.ListBuffer
/**
  * 统计最受欢迎歌曲前三名
  *
  * +-----+-----+
| song|times|
+-----+-----+
|song6|    3|
|song2|    2|
|song4|    2|
+-----+-----+

  +-----+-----+
| song|times|
+-----+-----+
|song6|    3|
|song2|    2|
|song4|    2|
+-----+-----+
  */
object MusicSongJob {
  def main(args: Array[String]): Unit = {
    //
    //
    val spark = SparkSession.builder()
      .master("local[2]")
      .appName("Ch09MusicGenderJobApp")
      .getOrCreate()
    import  spark.implicits._

    //1.得到df
    val df = spark.read.format("parquet").load("F:\\accp教学\\sparkresources\\clean13")
    //2.
  //  df.select("song").groupBy("song").agg(count("song").as("times")).orderBy(count("song").desc).limit(3).show()
    df.createOrReplaceTempView("music")
    val df2 = df.sqlContext.sql("select song,count(1) as times from music group by song order by times desc limit(3)")

    //3.将统计结果写入数据库
    df2.foreachPartition(x=>{
      //
      val list = new ListBuffer[MuSong]
      x.foreach(y=>{
        var song = y.getAs[String]("song")
        var times = y.getAs[Long]("times")
        list.append(MuSong(song,times))
      })
      MusicDao.insertMuSong(list)
    })
    spark.stop()
  }
}
